package cn.jji8.mysqlUse.mySqlSimpleUse;

import cn.jji8.mysqlUse.mySqlSimpleUse.column.Conlumn;
import cn.jji8.mysqlUse.mySqlSimpleUse.error.AddRowException;
import cn.jji8.mysqlUse.mySqlSimpleUse.error.CreateTableCxception;
import cn.jji8.mysqlUse.mySqlSimpleUse.error.ModifyLineException;
import cn.jji8.mysqlUse.mySqlSimpleUse.error.QueryTableException;
import cn.jji8.mysqlUse.mySqlSimpleUse.queryCondition.QueryCondition;
import cn.jji8.mysqlUse.mySqlSimpleUse.queryResults.QueryResults;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * 表，代表数据库中的一个表
 * */
public class SqlTable {
    //与数据库的连接
    SqlConnect sqlConnect;
    //表名
    String tableName;
    //列集合
    Set<Conlumn> ConlumnSet;
    SqlTable(SqlConnect sqlConnect, String tableName, Set<Conlumn> ConlumnSet) {
        this.sqlConnect = sqlConnect;
        this.tableName = tableName;
        this.ConlumnSet = new HashSet<>(ConlumnSet);
        try {
            SqlConnect.sqlStatement statement = sqlConnect.getStatement();
            //如果没有表就创建一个
            statement.execute("CREATE TABLE IF NOT EXISTS `"+tableName+"`(identifier INT PRIMARY KEY AUTO_INCREMENT)");
            String dbName;
            {//获取数据库名
                ResultSet resultSet = statement.executeQuery("select database() dbName");
                resultSet.next();
                dbName = resultSet.getString("dbName");
            }
            Set<String> coluumnNameSet = new HashSet<>();
            {//获取列名列表
                ResultSet resultSet = statement.executeQuery("select COLUMN_NAME from information_schema.COLUMNS where table_name = '"+tableName+"' AND Table_Schema = '"+dbName+"'");
                while (resultSet.next()){
                    coluumnNameSet.add(resultSet.getString("COLUMN_NAME"));
                }
            }
            for(Conlumn conlumn:ConlumnSet){
                //如果已经有这个列了，就什么都不做
                if(coluumnNameSet.contains(conlumn.getConlumnName())){
                    continue;
                }
                //如果没有这个列就添加一个列
                statement.execute("alter table `"+tableName+"` add column `"+conlumn.getConlumnName()+"` "+conlumn.getType());
            }
            //关闭连接
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new CreateTableCxception("创建表格命令执行异常",throwables);
        }
    }
    /**
     * 查询这个表，多个列的数据,用多个条件
     * 查询多个列的数据推荐使用这个，效率最高，能一次查完，尽量不查低二次。
     * @param conlumnSet 返回数据的列集合
     * @param queryConditionSet 数据的筛选条件集合
     * */
    public QueryResults query(Set<Conlumn> conlumnSet, Set<QueryCondition> queryConditionSet){
        try {
            SqlConnect.sqlStatement statement = sqlConnect.getStatement();

            StringBuilder command = new StringBuilder("select");
            command.append(" ");

            //获取ConlumnSet的迭代器
            Iterator<Conlumn> conlumniterator = conlumnSet.iterator();
            //添加第一个ID列
            command.append("identifier");
            //添加剩下全部的
            while (conlumniterator.hasNext()){
                command.append(",`");
                command.append(conlumniterator.next().getConlumnName());
                command.append("`");
            }

            //添加固定语法
            command.append(" ");
            command.append("from");
            command.append(" `");
            command.append(tableName);
            command.append("` ");
            command.append("where");
            command.append(" ");

            //添加条件
            Iterator<QueryCondition> queryConditionIterator = queryConditionSet.iterator();
            QueryCondition queryCondition1 = queryConditionIterator.next();
            command.append(queryCondition1.toString());
            while (queryConditionIterator.hasNext()){
                command.append(" AND ");
                command.append(queryConditionIterator.next().toString());
            }
            ResultSet resultSet = statement.executeQuery(command.toString());

            QueryResults qr = new QueryResults(resultSet, conlumnSet);
            //关闭连接
            statement.close();
            return qr;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new QueryTableException("执行查询命令时出现异常。",throwables);
        }
    }
    /**
     * 查询这个表,只查一个列的数据
     * @param conlumn 返回数据的列
     * @param queryConditionSet 数据的筛选条件集合
     * */
    public QueryResults query(Conlumn conlumn,Set<QueryCondition> queryConditionSet){
        Set<Conlumn> conlumnSet = new HashSet<>();
        conlumnSet.add(conlumn);
        return query(conlumnSet,queryConditionSet);
    }
    /**
     * 查询这个表,只用一个条件
     * @param conlumnSet 返回数据的列集合
     * @param queryCondition 数据的筛选条件
     * */
    public QueryResults query(Set<Conlumn> conlumnSet, QueryCondition queryCondition){
        Set<QueryCondition> queryConditionSet = new HashSet<>();
        queryConditionSet.add(queryCondition);
        return query(conlumnSet,queryConditionSet);
    }
    /**
     * 查询这个表,只查一个列的数据，只用一个条件
     * @param conlumn 返回数据的列
     * @param queryCondition 数据的筛选条件
     * */
    public QueryResults query(Conlumn conlumn, QueryCondition queryCondition){
        Set<Conlumn> conlumnSet = new HashSet<>();
        conlumnSet.add(conlumn);
        return query(conlumnSet,queryCondition);
    }
    /**
     * 添加行数据,多列都有数据
     * @param row Map[添加数据的列,数据]
     * */
    public void add(Map<Conlumn,Object> row){
        StringBuilder front = new StringBuilder("INSERT INTO `"+tableName+"`(");
        StringBuilder behind = new StringBuilder(" VALUES (");
        for(Conlumn conlumn:row.keySet()){
            Object obj = row.get(conlumn);
            front.append("`");
            front.append(conlumn.getConlumnName());
            front.append("`,");

            behind.append(conlumn.transformation(obj));
            behind.append(",");
        }
        front.deleteCharAt(front.length()-1);
        front.append(")");
        behind.deleteCharAt(behind.length()-1);
        behind.append(")");
        try {
            SqlConnect.sqlStatement statement = sqlConnect.getStatement();
            statement.execute(front.toString()+behind.toString());
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new AddRowException("没有某列，或某列数据错误",throwables);
        }
    }
    /**
     * 添加一行，一列有数据
     * @param conlumn 有数据的列
     * @param obj 这一列的数据
     * */
    public void add(Conlumn conlumn,Object obj){
        Map<Conlumn,Object> row = new HashMap<>(1);
        row.put(conlumn,obj);
        add(row);
    }
    /**
     * 删除一列
     * @param ID 这一列的ID，在查询数据的时候Map中的key就是ID
     * */
    public boolean delete(int ID){
        try {
            SqlConnect.sqlStatement statement = sqlConnect.getStatement();
            statement.execute("DELETE FROM `"+tableName+"` WHERE identifier="+ID);
            statement.close();
            return true;
        } catch (SQLException throwables) {
            return false;
        }
    }
    /**
     * 修改一列的数据
     * @param row map[修改数据的列,新值]
     * @param ID 这一列的ID
     * */
    public void Update(int ID,Map<Conlumn,Object> row){
        StringBuilder stringBuilder = new StringBuilder("UPDATE `"+tableName+"` SET ");
        for(Conlumn conlumn:row.keySet()){
            Object obj = row.get(conlumn);
            stringBuilder.append("`");
            stringBuilder.append(conlumn.getConlumnName());
            stringBuilder.append("`=");
            stringBuilder.append(conlumn.transformation(obj));
            stringBuilder.append(",");
        }
        stringBuilder.deleteCharAt(stringBuilder.length()-1);
        stringBuilder.append(" WHERE identifier=").append(ID);
        try {
            SqlConnect.sqlStatement statement = sqlConnect.getStatement();
            statement.execute(stringBuilder.toString());
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new ModifyLineException("表格中没有某一列或某一列的数据错误",throwables);
        }
    }


}
